{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps- Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "**Show the 'size' of each of the co72010 events. Size is the total number of students attending each event.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.L01</td>\n",
       "        <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T02</td>\n",
       "        <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T04</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T01</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T05</td>\n",
       "        <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T06</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T03</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.L02</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('co72013.L01', 430),\n",
       " ('co72013.T02', 98),\n",
       " ('co72013.T04', 90),\n",
       " ('co72013.T01', 80),\n",
       " ('co72013.T05', 70),\n",
       " ('co72013.T06', 58),\n",
       " ('co72013.T03', 20),\n",
       " ('co72013.L02', 20)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ut_event.id, SUM(ut_student.sze) size FROM\n",
    "  ut_event JOIN ut_attends ON (ut_event.id=ut_attends.event) JOIN\n",
    "    ut_student ON (ut_student.id=ut_attends.student)\n",
    "    WHERE ut_event.modle='co72010'\n",
    "    GROUP BY ut_event.id\n",
    "    ORDER BY size DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "**For each post-graduate module, show the size of the teaching team. (post graduate modules start with the code co7).**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>_c1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72002</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72003</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72004</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72005</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72006</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72010</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72011</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72012</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72016</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72017</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72018</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72021</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72026</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72033</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('co72002', 1),\n",
       " ('co72003', 2),\n",
       " ('co72004', 1),\n",
       " ('co72005', 2),\n",
       " ('co72006', 1),\n",
       " ('co72010', 2),\n",
       " ('co72011', 2),\n",
       " ('co72012', 1),\n",
       " ('co72016', 2),\n",
       " ('co72017', 1),\n",
       " ('co72018', 1),\n",
       " ('co72021', 1),\n",
       " ('co72026', 2),\n",
       " ('co72033', 1)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT DISTINCT ut_modle.id, ut_teaches.staff\n",
    "      FROM ut_teaches JOIN ut_event ON (\n",
    "          ut_teaches.event=ut_event.id) JOIN\n",
    "      ut_modle ON (ut_event.modle=ut_modle.id)\n",
    "      WHERE ut_modle.id LIKE 'co7%'\n",
    ")\n",
    "SELECT id, COUNT(staff) FROM t GROUP BY id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "**Give the full name of those modules which include events taught for fewer than 10 weeks.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Interactivity and the Internet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Internet Multimedia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Project</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Interactivity and the Internet',), ('Internet Multimedia',), ('Project',)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT name FROM\n",
    "(SELECT ut_modle.name\n",
    "  FROM ut_modle JOIN ut_event ON (\n",
    "      ut_modle.id=ut_event.modle) JOIN\n",
    "    ut_occurs ON (ut_event.id=ut_occurs.event)\n",
    "    GROUP BY ut_modle.name, ut_event.id\n",
    "    HAVING COUNT(ut_occurs.event) < 10) t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "**Identify those events which start at the same time as one of the co72010 lectures.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12004.T04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12004.T05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12005.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12005.T04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12006.L03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12008.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12012.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22005.T02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22005.T04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22005.T07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22005.T08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22006.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22008.T03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22008.T04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co32011.T03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co32014.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co32016.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co32018.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co32021.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co42001.L01</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">33 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('co12004.T04',),\n",
       " ('co12004.T05',),\n",
       " ('co12005.T01',),\n",
       " ('co12005.T04',),\n",
       " ('co12006.L03',),\n",
       " ('co12008.L01',),\n",
       " ('co12012.T01',),\n",
       " ('co22005.T02',),\n",
       " ('co22005.T04',),\n",
       " ('co22005.T07',),\n",
       " ('co22005.T08',),\n",
       " ('co22006.L02',),\n",
       " ('co22008.T03',),\n",
       " ('co22008.T04',),\n",
       " ('co32011.T03',),\n",
       " ('co32014.T01',),\n",
       " ('co32016.L01',),\n",
       " ('co32018.L01',),\n",
       " ('co32021.L01',),\n",
       " ('co42001.L01',),\n",
       " ('co42010.T01',),\n",
       " ('co42015.T01',),\n",
       " ('co72006.L01',),\n",
       " ('co72016.T01',),\n",
       " ('co72018.T01',),\n",
       " ('coh2451.T01',),\n",
       " ('coh8412555.L01',),\n",
       " ('coh8412555.T02',),\n",
       " ('coh8412585.T03',),\n",
       " ('coh8412605.L01',),\n",
       " ('coh8412605.T01',),\n",
       " ('coh8412615.T03',),\n",
       " ('coh8412615.T05',)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS ( -- time: week + dow + tod\n",
    "  SELECT ut_event.id, ut_event.modle, \n",
    "    CONCAT(ut_occurs.week, ut_event.dow, ut_event.tod) AS time FROM\n",
    "    ut_event JOIN ut_occurs ON (ut_event.id=ut_occurs.event)\n",
    ")\n",
    "SELECT DISTINCT a.id\n",
    "  FROM (SELECT * FROM t WHERE modle != 'co72010') a JOIN\n",
    "    (SELECT * FROM t WHERE modle = 'co72010') b ON (\n",
    "     a.time=b.time)\n",
    "    ORDER BY a.id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "**How many members of staff have contact time which is greater than the average?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>_c0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(19,)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT ut_teaches.staff, SUM(ut_event.duration) hrs\n",
    "    FROM ut_event JOIN ut_teaches ON (ut_event.id=ut_teaches.event) JOIN\n",
    "      ut_occurs ON (ut_occurs.event=ut_event.id)\n",
    "      GROUP BY ut_teaches.staff\n",
    "), tt AS (\n",
    "    SELECT SUM(hrs)/COUNT(*) AS avg_hrs FROM t\n",
    ")\n",
    "SELECT COUNT(*) FROM t JOIN tt\n",
    "  WHERE hrs>tt.avg_hrs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
